package com.demo2do.fms.admin.entity.report.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class FacilityDistanceReportSearch implements QueryBuilder {
    
    /** 选择的公司 */
    private List<Long> organizationIds;
    
    /** 起始地 */
    private Long fromLoadPlaceId;
    
    /** 目的地 */
    private Long toLoadPlaceId;
    
    /** 导出条数上限 */
    private Integer limit;

    private QueryFilter filter;
    
    private QueryOrder order;

    /**
     * the default constructor
     */
    public FacilityDistanceReportSearch() {
        
        order = new QueryOrder().add("c", "organization_name", "ASC")
                                .add("wa", "facility_name", "ASC");
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if(filter == null || refresh) {
            
            filter = new QueryFilter().addEquals("wa", "id", fromLoadPlaceId)
                                      .addEquals("wb", "id", toLoadPlaceId)
                                      .addSentence("c.id IN (:organizationIds)", "organizationIds", organizationIds);
                
        }
        
        return filter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(256);
        
        buffer.append("SELECT c.organization_name AS organizationName, ")
                     .append("wa.facility_name AS startFacility, ")
                     .append("wb.facility_name AS endFacility, ")
                     .append("TO_CHAR(nvl(d.facility_distance, 0), 99999990.99) AS facilityDistance, ")
                     .append("NVL(d.shipment_cycle, 0) AS shipmentCycle ")
             .append("FROM tbl_truck_facility_distance d, vw_transportation_company c, vw_load_place wa, vw_load_place wb ")
             .append("WHERE d.is_disabled = 0 AND wa.id = d.from_load_place_id AND wb.id = d.to_load_place_id AND d.organization_id = c.id ")
             .append(getPreparedFilter(false).clauseSentence() + order.sentence());
        
        String sentence = "SELECT * FROM (" + buffer.toString() + ") WHERE ROWNUM <=" + limit;
        return sentence;
        
    }

    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("startFacility", rs.getObject("startFacility"));
                result.put("endFacility", rs.getObject("endFacility"));
                result.put("facilityDistance", rs.getObject("facilityDistance"));
                result.put("shipmentCycle", rs.getObject("shipmentCycle"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return order;
    }
    
    /**
     * @return the organizationIds
     */
    public List<Long> getOrganizationIds() {
        return organizationIds;
    }

    /**
     * @return the fromLoadPlaceId
     */
    public Long getFromLoadPlaceId() {
        return fromLoadPlaceId;
    }

    /**
     * @return the toLoadPlaceId
     */
    public Long getToLoadPlaceId() {
        return toLoadPlaceId;
    }
    
    /**
     * @return
     */
    public Integer getLimit() {
        return limit;
    }
    
    /**
     * @param organizationIds the organizationIds to set
     */
    public void setOrganizationIds(List<Long> organizationIds) {
        this.organizationIds = organizationIds;
    }

    /**
     * @param fromLoadPlaceId the fromLoadPlaceId to set
     */
    public void setFromLoadPlaceId(Long fromLoadPlaceId) {
        this.fromLoadPlaceId = fromLoadPlaceId;
    }

    /**
     * @param toLoadPlaceId the toLoadPlaceId to set
     */
    public void setToLoadPlaceId(Long toLoadPlaceId) {
        this.toLoadPlaceId = toLoadPlaceId;
    }

    /**
     * @param limit
     */
    public void setLimit(Integer limit) {
        this.limit = limit;
    }
    
}
